﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Windows;
using Task_Network_Center.General;
using System.Data;
using System.Windows.Controls;
using System.Globalization;

namespace Task_Network_Center.DataBaseTable
{
    class TaskTable
    {
        MySqlConnection conn = null;
        MySqlConnection conn2 = null;


        public DataTable QueryTableReadable(string query, object[,] departementArray, object[,] employeArray, object[,] equipeArray, object[,] prioriteArray)
        {

            DataTable result = new DataTable();
            GCGGeneralLib.GeneralVar.DureeTotal = 0;
            GCGGeneralLib.GeneralVar.TempsRestantTotal = 0;

            result.Columns.Add("id_tache");
            result.Columns.Add("projets");
            result.Columns.Add("description");
            result.Columns.Add("inscrit_par");
            result.Columns.Add("date_dinscription");
            result.Columns.Add("solution");
            result.Columns.Add("executant");
            result.Columns.Add("duree");
            result.Columns.Add("progression");
            result.Columns.Add("avancement");
            result.Columns.Add("equipe");
            result.Columns.Add("priorite");
            result.Columns.Add("echeance");
            result.Columns.Add("departement");
            result.Columns.Add("complete_le");
            result.Columns.Add("complete_par");
            result.Columns.Add("note1");
            result.Columns.Add("note2");
            result.Columns.Add("note3");
            result.Columns.Add("note4");


            DataSet set = new DataSet();

            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            string project = "", description = "", inscritPar = "", solution = "", executant = "",  avancement = "", equipe = "", priorite = "", departement = "", completePar = "";
            string note1 = "", note2 = "", note3 = "", note4 = "";
            decimal duree = 0, progression = 0;
            
            DateTime? inscription = new DateTime();
            DateTime? echeance = new DateTime();
            DateTime? completeLe = new DateTime();

            decimal tempCompleteRow = 0, tempRestantRow = 0;

            try
            {
                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                

                while (dr.Read())
                {


                    int id = dr.GetInt32(0);
                    

                    if (dr.GetString(1) != "")
                    {
                         project = dr.GetString(1);
                    }

                    if (dr.GetString(2) != "")
                    {
                         description = dr.GetString(2);
                    }
                    else
                    {
                        description = "";
                    }


                    if (Convert.ToInt32(dr.GetInt32(3)) != -1)
                    {
                       
                        int cInscritPar = dr.GetInt32(3);
                        for (int i = 0; i < employeArray.GetLength(1); i++)
                        {
                            if (cInscritPar == Convert.ToInt32(employeArray[0, i]))
                            {
                                inscritPar = employeArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        inscritPar = "";
                    }

                    if (!dr.IsDBNull(4))
                    {
                    inscription = dr.GetDateTime(4);
                    
                    }
                    else
                    {
                    inscription = null;
                    
                    }


                    if (dr.GetString(5) != "")
                    {
                        solution = dr.GetString(5);
                    }
                    else
                    {
                        solution = "";
                    }


                    if (Convert.ToInt32(dr.GetInt32(6)) != -1)
                    {
                        
                        int cExecutant = dr.GetInt32(6);
                        for (int i = 0; i < employeArray.GetLength(1); i++)
                        {
                            if (cExecutant == Convert.ToInt32(employeArray[0, i]))
                            {
                                executant = employeArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        executant = "";
                    }


                    if (!dr.IsDBNull(7))
                    {
                        duree = dr.GetDecimal(7);
                    }
                    else
                    {
                        duree = 0;
                    }
                    GCGGeneralLib.GeneralVar.DureeTotal = GCGGeneralLib.GeneralVar.DureeTotal + duree;

                    if (!dr.IsDBNull(8))
                    {
                        avancement = dr.GetString(8);
                    }
                    else
                    {
                        avancement = "";
                    }


                    if (Convert.ToInt32(dr.GetInt32(9)) != -1)
                    {
                        
                        int cEquipe = dr.GetInt32(9);
                        for (int i = 0; i < equipeArray.GetLength(1); i++)
                        {
                            if (cEquipe == Convert.ToInt32(equipeArray[0, i]))
                            {
                                equipe = equipeArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        equipe = "";
                    }



                    if (Convert.ToInt32(dr.GetInt32(10)) != -1)
                    {
                       
                        int cPriorite = dr.GetInt32(10);
                        for (int i = 0; i < prioriteArray.GetLength(1); i++)
                        {
                            if (cPriorite == Convert.ToInt32(prioriteArray[0, i]))
                            {
                                priorite = prioriteArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        priorite = "";
                    }


                    if (!dr.IsDBNull(11))
                    {
                        echeance = dr.GetDateTime(11);
                    }
                    else
                    {
                        echeance = null;
                    }


                    if (Convert.ToInt32(dr.GetInt32(12)) != -1)
                    {
                        
                        int cDeparement = dr.GetInt32(12);
                        for (int i = 0; i < departementArray.GetLength(1); i++)
                        {
                            if (cDeparement == Convert.ToInt32(departementArray[0, i]))
                            {
                                departement = departementArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        departement = "";
                    }


                    if (!dr.IsDBNull(13))
                    {
                        completeLe = dr.GetDateTime(13);
                    }
                    else
                    {
                        completeLe = null;
                    }

                    if (Convert.ToInt32(dr.GetInt32(14)) != -1)
                    {
                        
                        int cCompletePar = dr.GetInt32(14);
                        for (int i = 0; i < employeArray.GetLength(1); i++)
                        {
                            if (cCompletePar == Convert.ToInt32(employeArray[0, i]))
                            {
                                completePar = employeArray[1, i].ToString();
                            }
                        }
                    }
                    else
                    {
                        completePar = "";
                    }




                    if (!dr.IsDBNull(15))
                    {
                        note1 = dr.GetString(15);
                    }
                    else
                    {
                        note1 = "";
                    }

                    if (!dr.IsDBNull(16))
                    {
                        note2 = dr.GetString(16);
                    }
                    else
                    {
                        note2 = "";
                    }

                    if (!dr.IsDBNull(17))
                    {
                        note3 = dr.GetString(17);
                    }
                    else
                    {
                        note3 = "";
                    }

                    if (!dr.IsDBNull(18))
                    {
                        note4 = dr.GetString(18);
                    }
                    else
                    {
                        note4 = "";
                    }

                    if (!dr.IsDBNull(19))
                    {
                        progression = dr.GetDecimal(19);
                    }
                    else
                    {
                        progression = 0;
                    }
                    tempCompleteRow = duree * progression / 100;
                    tempRestantRow = duree - tempCompleteRow;
                    GCGGeneralLib.GeneralVar.TempsRestantTotal = GCGGeneralLib.GeneralVar.TempsRestantTotal + tempRestantRow;

                    result.Rows.Add(new Object[] {id,project,description,inscritPar,inscription,solution,executant,duree,progression,avancement,equipe,priorite
                        ,echeance,departement,completeLe
                        ,completePar,note1,note2,note3,note4});
                    
                }
                
                conn.Close();
                dr.Close();
                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - QueryTableReadable: " + e);
                return null;
            }
        }


        public bool InsertRow(TextBox projets, TextBox description, ComboBox inscrit_par
        , DatePicker date_dinscription, TextBox solution, ComboBox executant, TextBox duree, TextBox progression
        , TextBox avancement, ComboBox equipe, ComboBox priorite, DatePicker echeance, ComboBox departement
        , DatePicker complete_le, ComboBox complete_par, TextBox note1, TextBox note2, TextBox note3
        , TextBox note4, object[,] departementArray, object[,] employeArray, object[,] equipeArray, object[,] prioriteArray)
        {
            String query = "INSERT INTO tablelistdestaches_general (id_tache,projets,description,inscrit_par,date_dinscription,"
        + "solution,executant,duree,progression,avancement,equipe,priorite,echeance,departement,complete_le,complete_par,note1,"
        + "note2,note3,note4) VALUE (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20)";
            int id = TaskID();
            
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", id);
                cmd.Parameters.AddWithValue("p2", projets.Text);
                cmd.Parameters.AddWithValue("p3", description.Text);

                if (inscrit_par.SelectedIndex != -1)
                {
                    for (int i = 0; i < employeArray.GetLength(1); i++)
                    {
                        if (inscrit_par.SelectedValue.ToString() == employeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p4", employeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p4", -1);
                }


                if (date_dinscription.Text != "")
                {
                    cmd.Parameters.AddWithValue("p5", Convert.ToDateTime(date_dinscription.Text));
                }
                else
                {
                    cmd.Parameters.AddWithValue("p5", DBNull.Value);
                }
                
                cmd.Parameters.AddWithValue("p6", solution.Text);


                if (executant.SelectedIndex != -1)
                {
                    for (int i = 0; i < employeArray.GetLength(1); i++)
                    {
                        if (executant.SelectedValue.ToString() == employeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p7", employeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p7", -1);
                }


                cmd.Parameters.AddWithValue("p8",Convert.ToDecimal(duree.Text));
                cmd.Parameters.AddWithValue("p9", Convert.ToDecimal(progression.Text));
                cmd.Parameters.AddWithValue("p10", avancement.Text);


                if (equipe.SelectedIndex != -1)
                {
                    for (int i = 0; i < equipeArray.GetLength(1); i++)
                    {
                        if (equipe.SelectedValue.ToString() == equipeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p11", equipeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p11", -1);
                }


                if (priorite.SelectedIndex != -1)
                {
                    for (int i = 0; i < prioriteArray.GetLength(1); i++)
                    {
                        if (priorite.SelectedValue.ToString() == prioriteArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p12", prioriteArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p12", -1);
                }

                if (echeance.Text != "")
                {
                    cmd.Parameters.AddWithValue("p13", Convert.ToDateTime(echeance.Text));
                }
                else
                {
                    cmd.Parameters.AddWithValue("p13", DBNull.Value);
                }
                

                if(departement.SelectedIndex != -1)
                {
                    for (int i = 0; i < departementArray.GetLength(1); i++)
                    {
                        if (departement.SelectedValue.ToString() == departementArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p14", departementArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p14", -1);
                }

                if (complete_le.Text != "")
                {
                    cmd.Parameters.AddWithValue("p15", Convert.ToDateTime(complete_le.Text));  
                }
                else
                {
                    cmd.Parameters.AddWithValue("p15", DBNull.Value);
                }

                if (complete_par.SelectedIndex != -1)
                {
                    for (int i = 0; i < employeArray.GetLength(1); i++)
                    {
                        if (complete_par.SelectedValue.ToString() == employeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p16", employeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p16", -1);
                }


                cmd.Parameters.AddWithValue("p17", note1.Text);
                cmd.Parameters.AddWithValue("p18", note2.Text);
                cmd.Parameters.AddWithValue("p19", note3.Text);
                cmd.Parameters.AddWithValue("p20", note4.Text);
                


                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - InsertRow: " + e);
                return false; 
            }
            
        }



        public bool UpdateRow(int id, TextBox projets, TextBox description, ComboBox inscrit_par
        , DatePicker date_dinscription, TextBox solution, ComboBox executant, TextBox duree, TextBox progression
        , TextBox avancement, ComboBox equipe, ComboBox priorite, DatePicker echeance, ComboBox departement
        , DatePicker complete_le, ComboBox complete_par, TextBox note1, TextBox note2, TextBox note3
        , TextBox note4, object[,] departementArray, object[,] employeArray, object[,] equipeArray, object[,] prioriteArray)
        {
            String query = "UPDATE tablelistdestaches_general SET projets = @p1,description = @p2,inscrit_par = @p3,date_dinscription = @p4,"
                    + "solution = @p5,executant = @p6,duree = @p7,progression = @p8,avancement = @p9,equipe = @p10,priorite = @p11,echeance = @p12,departement = @p13,"
                    + "complete_le = @p14,complete_par = @p15,note1 = @p16,"
                    + "note2 = @p17,note3 = @p18,note4 = @p19 WHERE id_tache = @p20";
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", projets.Text);
                cmd.Parameters.AddWithValue("p2", description.Text);

                if (inscrit_par.SelectedIndex != -1)
                {
                    for (int i = 0; i < employeArray.GetLength(1); i++)
                    {
                        if (inscrit_par.SelectedValue.ToString() == employeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p3", employeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p3", -1);
                }

                if (date_dinscription.Text != "")
                {
                    cmd.Parameters.AddWithValue("p4", Convert.ToDateTime(date_dinscription.Text));
                }
                else
                {
                    cmd.Parameters.AddWithValue("p4", DBNull.Value);
                }

                    cmd.Parameters.AddWithValue("p5", solution.Text);

                    if (executant.SelectedIndex != -1)
                    {
                        for (int i = 0; i < employeArray.GetLength(1); i++)
                        {
                            if (executant.SelectedValue.ToString() == employeArray[1, i].ToString())
                            {
                                cmd.Parameters.AddWithValue("p6", employeArray[0, i]);
                            }

                        }
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("p6", -1);
                    }

                cmd.Parameters.AddWithValue("p7",Convert.ToDecimal(duree.Text));
                cmd.Parameters.AddWithValue("p8", Convert.ToDecimal(progression.Text));
                cmd.Parameters.AddWithValue("p9", avancement.Text);


                if (equipe.SelectedIndex != -1)
                {
                    for (int i = 0; i < equipeArray.GetLength(1); i++)
                    {
                        if (equipe.SelectedValue.ToString() == equipeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p10", equipeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p10", -1);
                }

                if (priorite.SelectedIndex != -1)
                {
                    for (int i = 0; i < prioriteArray.GetLength(1); i++)
                    {
                        if (priorite.SelectedValue.ToString() == prioriteArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p11", prioriteArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p11", -1);
                }

                if (echeance.Text != "")
                {
                    cmd.Parameters.AddWithValue("p12", Convert.ToDateTime(echeance.Text));
                }
                else
                {
                    cmd.Parameters.AddWithValue("p12", DBNull.Value);
                }


                if (departement.SelectedIndex != -1)
                {
                    for (int i = 0; i < departementArray.GetLength(1); i++)
                    {
                        if (departement.SelectedValue.ToString() == departementArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p13", departementArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p13", -1);
                }

                if (complete_le.Text != "")
                {
                    cmd.Parameters.AddWithValue("p14", Convert.ToDateTime(complete_le.Text));  
                }
                else
                {
                    cmd.Parameters.AddWithValue("p14", DBNull.Value);
                }


                if (complete_par.SelectedIndex != -1)
                {
                    for (int i = 0; i < employeArray.GetLength(1); i++)
                    {
                        if (complete_par.SelectedValue.ToString() == employeArray[1, i].ToString())
                        {
                            cmd.Parameters.AddWithValue("p15", employeArray[0, i]);
                        }

                    }
                }
                else
                {
                    cmd.Parameters.AddWithValue("p15", -1);
                }

                cmd.Parameters.AddWithValue("p16", note1.Text);
                cmd.Parameters.AddWithValue("p17", note2.Text);
                cmd.Parameters.AddWithValue("p18", note3.Text);
                cmd.Parameters.AddWithValue("p19", note4.Text);
                cmd.Parameters.AddWithValue("p20", id);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;

            }
            catch (Exception e)
            {

                MessageBox.Show("Erreur TaskTable - UpdateRow: " + e);
                return false;
            }
            
        }

        public string[] ProjectListArray()
        {
            string[] result = new string[GetDistinctProjectCount()];
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            string query = "SELECT DISTINCT projets FROM tablelistdestaches_general ORDER BY projets";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            
            try
            {
                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                int i = 0;
                while (dr.Read())
                {
                    result[i] = dr.GetString(0);
                    i++;
                }
                i = 0;
                conn.Close();
                dr.Close();
                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - ProjectListArray: " + e);
                return null;
            }
        }

        public bool DeleteRow(int id)
        {
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            String query = "DELETE FROM tablelistdestaches_general WHERE id_tache = @p1";
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", id);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - DeleteRow: " + e);
                return false;
            }
        }

        private Int64 GetDistinctProjectCount()
        {
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();

                String query = "SELECT COUNT(DISTINCT projets) FROM tablelistdestaches_general";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                conn.Open();

                Int64 result = (Int64)cmd.ExecuteScalar();

                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - GetDistinctProjectCount: " + e);
                return -1;
            }
        }

        private int TaskID()
        {
            
            conn2 = GCGGeneralLib.DataBaseConnection.DbConnection();
            String query = "SELECT LAST_INSERT_ID() FROM tablelistdestaches_general";
            MySqlCommand cmd = conn2.CreateCommand();
            cmd.CommandText = query;
            try
            {
                
                conn2.Open();
                cmd.ExecuteScalar();
                int count = Convert.ToInt32(cmd.LastInsertedId);
                conn2.Close();
                conn2 = null;
                return count+1;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TaskTable - TaskID: " + e);
                return -1;
            }

        }

        
    }
    
}
